package com.cmpe.av.logic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMessage.RecipientType;

import com.cmpe.av.model.Comment;
import com.cmpe.av.model.Report;
import com.cmpe.av.model.ObstacleType;
import com.cmpe.av.model.Subscription;
import com.cmpe.av.model.Tag;
import com.cmpe.av.model.User;

public class ReportLogic {

	private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;
	private final String USERNAME = "project6";
	private final String PASSWORD = "17Souf";
	private final String URL = "jdbc:mysql://titan.cmpe.boun.edu.tr:3306/database6";

	public ReportLogic() throws Exception{
		try {

			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager
					.getConnection(URL,USERNAME,PASSWORD);  
		} catch (Exception e) {
			throw e;
		} 
	};


	public int addReport(String reportName, int obstacleID, int UserID , double latitude , double longitude , int Status , String Description , int Rate , 
			int ImageNum,int videoNum,int audioNum) throws SQLException
			{
		try{		
			String sql = "INSERT INTO  `database6`.`Reports` (`ReportID` ," +
					"`ReportName` ,`ObstacleID` ,`UserID` ,`latitude` ,`longitude`, `Status` ,`CreatedOn` ,`Description` ," +
					"`Rate` ,`UpdateTime` ,`ImageNum`,`VideoNum`,`AudioNum`) " +
					"VALUES (NULL , '"+reportName+"',  '"+obstacleID+"', '"+UserID+"',  '"+latitude+"','"+longitude+"', '"+Status+"', CURRENT_TIMESTAMP , '"+Description+"' , " +
					"'"+Rate+"', NOW( ) , '"+ImageNum+"','"+videoNum+"','"+audioNum+"' )";
			PreparedStatement statement = connect.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			int auto_id = rs.getInt(1);
			return auto_id;
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
			return -1;
		}
			}

	// not used now

	public boolean IsReportVotedByUserIdAndReportId (long UserID, long  ReportID)
	{
		boolean status = false;
		try{
			statement = connect.createStatement();
			String query = "SELECT * FROM ReportRate WHERE ReportID ='" + ReportID +"'"+ "AND UserID = '"+UserID+"'";
			resultSet = statement.executeQuery(query);
			if(resultSet.next()){
				status =  true;
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return status;
	}
	
	public void insertReportRate (long UserID, long ReportID)
	{
		try {
			statement = connect.createStatement();
			statement.executeUpdate("INSERT INTO ReportRate(ReportID,UserID) " + "VALUES ("+ReportID+","+ UserID+")");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	// onur

	public void addTags(long report_id, String[] tags) throws SQLException
	{
		try{
			for(int i=0;i<tags.length;i++){

				Statement stat = connect.createStatement();
				ArrayList<String> existingTags = new ArrayList<String>();
				
				List<String> existingTagsInReport = new ArrayList<String>();
				existingTagsInReport = getTagsInReport(report_id);
				
				String query2 = "Select TagID from ReportTag where ReportID=" + report_id;
				
				String query = "SELECT TagName from Tag";
				ResultSet rs1 = stat.executeQuery(query);		
				

				String sql = "INSERT INTO  `database6`.`Tag` (`TagID` ," +
						"`TagName`) " +
						"VALUES (NULL , '"+tags[i]+"')";

				PreparedStatement statement = connect.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				String queryTag = "SELECT TagID from Tag WHERE TagName='" + tags[i]+"'";
				Statement st = connect.createStatement();
				while(rs1.next())
				{
					existingTags.add(rs1.getString("TagName"));					
				}
				
//				while(rs2.next())
//				{
//					existingTagsInReport.add(rs2.getLong("TagID"));
//				}

				//System.out.println("Warning!");
				if(!existingTags.contains(tags[i]))
					statement.executeUpdate();

				ResultSet rs = st.executeQuery(queryTag);
				int tag_id = -1;
				if(rs.next())
					tag_id = rs.getInt(1);

				String sql_1 = "INSERT INTO  `database6`.`ReportTag` (`ReportTagID` ," +
						"`ReportID`,`TagID`) " +
						"VALUES (NULL , '"+report_id+"', '"+tag_id+"')";
				PreparedStatement statement_1 = connect.prepareStatement(sql_1);
				if(!existingTagsInReport.contains(tags[i]))
					statement_1.executeUpdate();
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

	}

	public String getStatusName(int statusId){
		String statusName="";
		if(statusId==0)
			statusName="Detected";
		else if(statusId==1)
			statusName="InProgress";
		else if(statusId==2)
			statusName="Fixed";
		else if(statusId==3)
			statusName="FriendlyLocation";
		return statusName;
	}

	public int getStatus(String statusName){
		if(statusName==null)
			statusName="";
		int status=0; //for "Detected"
		if(statusName.equals("InProgress"))
			status=1;
		else if(statusName.equals("Fixed"))
			status=2;
		else if(statusName.equals("FriendlyLocation"))
			status=3;
		return status;
	}
	public int getObstacleId(String obstacleType)
	{
		int obstacleId=0;
		try
		{
			statement = connect.createStatement();
			String query = "SELECT ObstacleID FROM ObstacleType WHERE ObstacleName ='" + obstacleType +"'";
			resultSet = statement.executeQuery(query);
			while(resultSet.next())
			{
				obstacleId = resultSet.getInt("ObstacleID");
			}

		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return obstacleId;		
	}

	public String getObstacleName(long obstacleId)
	{
		String obsName="";
		try
		{
			statement = connect.createStatement();
			String query = "SELECT ObstacleName FROM ObstacleType WHERE ObstacleID =" + obstacleId ;
			resultSet = statement.executeQuery(query);
			while(resultSet.next())
			{
				obsName = resultSet.getString("ObstacleName");
			}

		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return obsName;		
	}
	public Report viewReport(Long ReportID)
	{
		Report report = new Report();

		try{

			statement = connect.createStatement();
			String query = "SELECT * FROM Reports WHERE ReportID =" + ReportID;
			resultSet = statement.executeQuery(query);

			//			while(resultSet.getInt(1) >1)
			//			{
			//				System.err.println ("Error message: " );
			//				break;
			//			}
			while(resultSet.next()){

				report.setAll(resultSet.getLong("ReportID"), resultSet.getString("ReportName"), 
						resultSet.getLong("UserID"), 
						resultSet.getLong("ObstacleID"), 
						resultSet.getDouble("latitude"),
						resultSet.getDouble("longitude"),
						resultSet.getInt("Status"),
						resultSet.getInt("ImageNum"),
						resultSet.getString("Description"),
						resultSet.getDate("CreatedOn"),
						resultSet.getDate("UpdateTime"),
						resultSet.getInt("rate"));
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return report;
	}
	public List<ObstacleType> getObstacles()
	{
		List<ObstacleType> obstacles = new ArrayList<ObstacleType>();

		try
		{
			statement = connect.createStatement();
			String query = "SELECT * FROM ObstacleType ";
			resultSet = statement.executeQuery(query);
			while(resultSet.next())
			{
				long obsID = resultSet.getLong("ObstacleID");
				String obsName = resultSet.getString("ObstacleName");
				ObstacleType oT = new ObstacleType(obsID,obsName);
				obstacles.add(oT);
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return obstacles;
	}

	public List<Tag> getTags()
	{
		List<Tag> tags = new ArrayList<Tag>();

		try
		{
			statement = connect.createStatement();
			String query = "SELECT * FROM Tag order by TagName";
			resultSet = statement.executeQuery(query);
			while(resultSet.next())
			{
				long tagID = resultSet.getLong("TagID");
				String tagName = resultSet.getString("TagName");
				Tag tag = new Tag(tagID,tagName);
				tags.add(tag);
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return tags;
	}

	public List<String> getTagsInReport(long ReportID)
	{
		List<String> tags = new ArrayList<String>();
		List tagIDs = new ArrayList();

		try
		{
			statement = connect.createStatement();
			String query = "SELECT TagID FROM ReportTag WHERE ReportID =" + ReportID;
			resultSet = statement.executeQuery(query);
			while (resultSet.next()) 
			{
				tagIDs.add(resultSet.getLong("TagID"));   
			}   

			int i;

			for(i=0; i<tagIDs.size(); i++)
			{
				query = "SELECT TagName FROM Tag WHERE TagID =" + tagIDs.get(i);
				resultSet = statement.executeQuery(query);
				while(resultSet.next())
				{
					String tag = resultSet.getString("TagName");
					tags.add(tag);
				}				
			}

		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return tags;
	}
	//returns comments of a report
	public List<Comment> getComments(long ReportID)
	{
		List<Comment> comments = new ArrayList<Comment>();
		List tagIDs = new ArrayList();

		try
		{
			statement = connect.createStatement();
			String query = "SELECT * FROM Comments WHERE ReportID =" + ReportID;
			resultSet = statement.executeQuery(query);
			while (resultSet.next()) 
			{
				long commentID = resultSet.getLong("CommentID");
				long reportID = resultSet.getLong("ReportID");
				String text = resultSet.getString("Text");
				Date createdOn = resultSet.getDate("CreatedOn");
				long userID = resultSet.getLong("UserID");
				Comment com = new Comment(commentID,reportID,text,createdOn,userID);
				comments.add(com);
			}   

		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return comments;
	}
	public Date getReportDate(long ReportId)
	{
		Date created = new Date();
		try{
			statement = connect.createStatement();
			String query = "SELECT CreatedOn FROM Reports WHERE ReportID =" + ReportId;
			resultSet = statement.executeQuery(query);

			if (resultSet.next()) 
			{
				created = resultSet.getDate("CreatedOn");   
			} 
		}
		catch(Exception e)
		{
			System.err.println("Error message: " +e.getMessage());
		}
		return created;
	}

	public List<Report> searchReport(String reportType, double latitude, double longitude, String tag, String status, int by)
	{
		List<Report> tempreports1 = new ArrayList<Report>(); //type
		List<Report> tempreports2 = new ArrayList<Report>(); //location
		List<Report> tempreports3 = new ArrayList<Report>(); //tag
		List<Report> tempreports4 = new ArrayList<Report>(); //status
		List<Report> reports = new ArrayList<Report>();

		tempreports1 = searchReportbyType(reportType, by);
		tempreports2 = searchReportbyLocation(latitude, longitude, by);
		tempreports3 = searchReportbyTag(tag, by);
		tempreports4 = searchReportbyStatus(status, by);

		if(status.equals("null"))
		{
			if(tag.equals("null"))
			{

				if(!reportType.equals("null") && latitude == 0.0)
					reports = tempreports1;

				else if(reportType.equals("null") && latitude != 0.0)
					reports = tempreports2;

				else if(!reportType.equals("null") && latitude != 0.0)
				{
			//		System.out.println("come here");
					reports = tempreports1;
					reports.retainAll(tempreports2);
				}
			}
			else if(!tag.equals("null"))
			{
				if(reportType.equals("null") && latitude == 0.0)
					reports = tempreports3;
				if(!reportType.equals("null") && latitude == 0.0)
				{
					reports = tempreports1;
					reports.retainAll(tempreports3);
				}
				if(reportType.equals("null") && latitude != 0.0)
				{
					reports = tempreports2;
					reports.retainAll(tempreports3);
				}
				if(!reportType.equals("null") && latitude != 0.0)
				{
					reports = tempreports1;
					reports.retainAll(tempreports2);
					reports.retainAll(tempreports3);
				}
			}
		}
		else if(!status.equals("null"))
		{
			if(tag.equals("null"))
			{
				if(reportType.equals("null") && latitude == 0.0)
				{
			//		System.out.println("i am in correct place");
					reports = tempreports4;
				}

				else if(!reportType.equals("null") && latitude == 0.0)
				{
					reports = tempreports1;
					reports.retainAll(tempreports4);
				}

				else if(reportType.equals("null") && latitude != 0.0)
				{
					reports = tempreports2;
					reports.retainAll(tempreports4);
				}

				else if(!reportType.equals("null") && latitude != 0.0)
				{
				//	System.out.println("come here");
					reports = tempreports1;
					reports.retainAll(tempreports2);
					reports.retainAll(tempreports4);
				}
			}
			else if(!tag.equals("null"))
			{
				if(reportType.equals("null") && latitude == 0.0)
				{
					reports = tempreports3;
					reports.retainAll(tempreports4);
				}

				else if(!reportType.equals("null") && latitude == 0.0)
				{
					reports = tempreports1;
					reports.retainAll(tempreports3);
					reports.retainAll(tempreports4);
				}
				else if(reportType.equals("null") && latitude != 0.0)
				{
					reports = tempreports2;
					reports.retainAll(tempreports3);
					reports.retainAll(tempreports4);
				}
				else if(!reportType.equals("null") && latitude != 0.0)
				{
					reports = tempreports1;
					reports.retainAll(tempreports2);
					reports.retainAll(tempreports3);
					reports.retainAll(tempreports4);
				}
			}

		}

		return reports;				
	}

	public List<Report> searchReportbyStatus(String status, int by)
	{
		List<String> reportIDs = new ArrayList<String>();
		List<Report> reports = new ArrayList<Report>();
		int statusid = getStatus(status);
//		System.out.println("Status id is" + statusid);
		try{

			statement = connect.createStatement();
			String query;
			if(by==0)
				query= "SELECT ReportID FROM Reports WHERE Status ='" + statusid +"' order by ReportID desc";
			else
				query= "SELECT ReportID FROM Reports WHERE Status ='" + statusid +"' order by Rate desc";
			resultSet = statement.executeQuery(query);

			while (resultSet.next()) 
			{
				reportIDs.add(resultSet.getString("ReportID"));   
			} 

			for(int i=0; i<reportIDs.size(); i++)
			{
				query = "SELECT ReportName, ReportID, latitude, longitude, Status, Rate, CreatedOn FROM Reports WHERE ReportID ='" + reportIDs.get(i) + "' order by ReportID desc";
				resultSet = statement.executeQuery(query);
				while(resultSet.next())
				{
					Report rpt = new Report();
					rpt.setReport_id(resultSet.getLong("ReportID"));
					rpt.setReportName(resultSet.getString("ReportName"));
					rpt.setLatitude(resultSet.getDouble("latitude"));
					rpt.setLongitude(resultSet.getDouble("longitude"));
					rpt.setStatus(resultSet.getInt("Status"));
					rpt.setRate(resultSet.getInt("Rate"));
					rpt.setCreated_on(resultSet.getDate("CreatedOn"));
					//					if(rpt.getCreated_on() == null)
					//						rpt.setCreated_on(getReportDate(rpt.getReport_id()));
					reports.add(rpt);
				}				
			}

		}

		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}


		return reports;

	}

	public List<Report> searchReportbyTag(String tag, int by)
	{
		List<String> reportIDs = new ArrayList<String>();
		List<Report> reports = new ArrayList<Report>();
		int tagId = 0;
		try{

			statement = connect.createStatement();
			String query = "SELECT TagID FROM Tag WHERE TagName ='" + tag +"'";
			resultSet = statement.executeQuery(query);

			while(resultSet.next())
			{
				tagId = resultSet.getInt("TagID");
			}

			query = "SELECT ReportID FROM ReportTag WHERE TagID =" + tagId;
			resultSet = statement.executeQuery(query);

			while (resultSet.next()) 
			{
				reportIDs.add(resultSet.getString("ReportID"));   
			}   

			int i;

			for(i=0; i<reportIDs.size(); i++)
			{
				if(by==0)
					query = "SELECT ReportName, ReportID, latitude, longitude, Status, Rate, CreatedOn FROM Reports WHERE ReportID ='" + reportIDs.get(i) + "' order by ReportID desc";
				else
					query = "SELECT ReportName, ReportID, latitude, longitude, Status, Rate, CreatedOn FROM Reports WHERE ReportID ='" + reportIDs.get(i) + "' order by Rate desc";
				
				resultSet = statement.executeQuery(query);
				while(resultSet.next())
				{
					Report rpt = new Report();
					rpt.setReport_id(resultSet.getLong("ReportID"));
					rpt.setReportName(resultSet.getString("ReportName"));
					rpt.setLatitude(resultSet.getDouble("latitude"));
					rpt.setLongitude(resultSet.getDouble("longitude"));
					rpt.setStatus(resultSet.getInt("Status"));
					rpt.setRate(resultSet.getInt("Rate"));
					rpt.setCreated_on(resultSet.getDate("CreatedOn"));
					//					if(rpt.getCreated_on() == null)
					//						rpt.setCreated_on(getReportDate(rpt.getReport_id()));
					reports.add(rpt);
				}				
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return reports;
	}

	public List<Report> searchReportbyType(String reportType, int by)
	{
		List obstacleIDs = new ArrayList();
		List<Report> reports = new ArrayList<Report>();
		int reportTypeId = 0;
		try{

			statement = connect.createStatement();
			String query = "SELECT ReportTypeID FROM ReportType WHERE ReportTypeName ='" + reportType+"'";
			resultSet = statement.executeQuery(query);

			while(resultSet.next())
			{
				reportTypeId = resultSet.getInt("ReportTypeID");
			}

			query = "SELECT ObstacleID FROM ReportObstacle WHERE ReportTypeID =" + reportTypeId;
			resultSet = statement.executeQuery(query);

			while (resultSet.next()) 
			{
				obstacleIDs.add(resultSet.getString("ObstacleID"));   
			}   

			int i;

			for(i=0; i<obstacleIDs.size(); i++)
			{
				if(by==0)
					query = "SELECT ReportName, ReportID, latitude, longitude, Status, Rate, CreatedOn FROM Reports WHERE ObstacleID ='" + obstacleIDs.get(i) + "'  order by ReportID desc";
				else
					query = "SELECT ReportName, ReportID, latitude, longitude, Status, Rate, CreatedOn FROM Reports WHERE ObstacleID ='" + obstacleIDs.get(i) + "'  order by Rate desc";
					
				resultSet = statement.executeQuery(query);
				while(resultSet.next())
				{
					Report rpt = new Report();
					rpt.setReport_id(resultSet.getLong("ReportID"));
					rpt.setReportName(resultSet.getString("ReportName"));
					rpt.setLatitude(resultSet.getDouble("latitude"));
					rpt.setLongitude(resultSet.getDouble("longitude"));
					rpt.setStatus(resultSet.getInt("Status"));
					rpt.setRate(resultSet.getInt("Rate"));
					rpt.setCreated_on(resultSet.getDate("CreatedOn"));
					//					if(rpt.getCreated_on() == null)
					//						rpt.setCreated_on(getReportDate(rpt.getReport_id()));
					reports.add(rpt);
				}				
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return reports;
	}

	public List<Report> searchReportbyLocation(double latitude, double longitude, int by)
	{

		List<Report> reports = new ArrayList<Report>();

		double lat1, lat2, long1, long2;
		lat1 = latitude + 0.004;
		lat2 = latitude - 0.004;
		long1 =	longitude + 0.01;
		long2 = longitude - 0.01;
		try{
			statement = connect.createStatement();
			String query;
			if(by==0)
				query = "SELECT ReportID, ReportName, latitude, longitude, Status, Rate, CreatedOn FROM Reports WHERE latitude< '"+lat1+"' and latitude >'" + lat2 + "'" +
					" and longitude <'" + long1 + "' and longitude >'" + long2 + "'" + "order by ReportID desc";
			else
				query = "SELECT ReportID, ReportName, latitude, longitude, Status, Rate, CreatedOn FROM Reports WHERE latitude< '"+lat1+"' and latitude >'" + lat2 + "'" +
						" and longitude <'" + long1 + "' and longitude >'" + long2 + "'" + "order by Rate desc";	
			resultSet = statement.executeQuery(query);

			while(resultSet.next())
			{	
				Report rpt = new Report();
				rpt.setReport_id(resultSet.getLong("ReportID"));
				rpt.setReportName(resultSet.getString("ReportName"));
				rpt.setLatitude(resultSet.getDouble("latitude"));
				rpt.setLongitude(resultSet.getDouble("longitude"));
				rpt.setStatus(resultSet.getInt("Status"));
				rpt.setRate(resultSet.getInt("Rate"));
				rpt.setCreated_on(resultSet.getDate("CreatedOn"));
				//				if(rpt.getCreated_on() == null)
				//					rpt.setCreated_on(getReportDate(rpt.getReport_id()));
				reports.add(rpt);
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return reports;
	}



	//returns user list who subscribed to related report
	public List<String> getMailsofUsers(long reportID)
	{
		List<String> userIDs = new ArrayList<String>();
		List<String> reports = new ArrayList<String>();

		try{

			statement = connect.createStatement();
			String query = "SELECT UserID FROM Subscription WHERE ReportID ='" + reportID +"'" + "AND Status = true";
			resultSet = statement.executeQuery(query);

			while(resultSet.next())
			{
				userIDs.add(resultSet.getString("userID"));
			}

			int i;

			for(i=0; i<userIDs.size(); i++)
			{
				query = "SELECT email FROM Users WHERE UserID =" + userIDs.get(i);
				resultSet = statement.executeQuery(query);
				while(resultSet.next())
				{					
					reports.add(resultSet.getString("email"));
				}				
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return reports;
	}
	public List<Report> searchSubscriptions(long userID)
	{
		List<String> reportIDs = new ArrayList<String>();
		List<Report> reports = new ArrayList<Report>();

		try{

			statement = connect.createStatement();
			String query = "SELECT ReportID FROM Subscription WHERE UserID ='" + userID +"'" + "AND Status = true ORDER BY SubscriptionID desc";
			resultSet = statement.executeQuery(query);

			while(resultSet.next())
			{
				reportIDs.add(resultSet.getString("ReportID"));
			}

			int i;
			for(i=0; i<reportIDs.size(); i++)
			{
				query = "SELECT * FROM Reports WHERE ReportID =" + reportIDs.get(i);
				resultSet = statement.executeQuery(query);
				while(resultSet.next())
				{
					Report rpt = new Report();
					rpt.setReport_id(resultSet.getLong("ReportID"));
					rpt.setReportName(resultSet.getString("ReportName"));
					rpt.setDescription(resultSet.getString("Description"));
					rpt.setStatus(resultSet.getInt("Status"));

					reports.add(rpt);
				}				
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return reports;
	}
	public List<Report> viewOwnReports(long userID)
	{
		List<Report> reports = new ArrayList<Report>();

		try{

			statement = connect.createStatement();
			String query = "SELECT * FROM Reports WHERE UserID ='" + userID +"' ORDER BY ReportID desc";
			resultSet = statement.executeQuery(query);

			while(resultSet.next())
			{
				Report rpt = new Report();
				rpt.setReport_id(resultSet.getLong("ReportID"));
				rpt.setReportName(resultSet.getString("ReportName"));
				rpt.setDescription(resultSet.getString("Description"));
				rpt.setStatus(resultSet.getInt("Status"));
				reports.add(rpt);
			}

		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}

		return reports;
	}

	public void sendMails(List<String> mails,String subject,String text){

		for(int i=0; i<mails.size(); i++)
		{
			sendMail(mails.get(i),subject,text);					
		}	
	}

	public void sendMail(String mail,String subject,String body){
		String host = "smtp.gmail.com";
		String userid = "accesibilitymanager"; 
		String password = "17Souf00"; 
		try
		{
			Properties props = System.getProperties(); 
			props.put("mail.smtp.starttls.enable", "true"); 
			props.put("mail.smtp.host", host); 
			props.setProperty("mail.transport.protocol", "smtps");
			props.put("mail.smtp.user", userid); 
			props.put("mail.smtp.password", password); 
			props.put("mail.smtp.port", "465"); 
			props.put("mail.smtps.auth", "true"); 
			Session session = Session.getDefaultInstance(props, null); 
			MimeMessage message = new MimeMessage(session); 
			InternetAddress fromAddress = null;
			InternetAddress toAddress = null;

			try {
				fromAddress = new InternetAddress("accesibilitymanager@gmail.com");
				toAddress = new InternetAddress(mail);
			} catch (AddressException e) {
				e.printStackTrace();
			}
			message.setFrom(fromAddress);
			message.setRecipient(RecipientType.TO, toAddress);
			message.setSubject(subject);	
			message.setText(body); 

			Transport transport = session.getTransport("smtps"); 
			transport.connect(host, userid, password); 
			transport.sendMessage(message, message.getAllRecipients()); 
			transport.close(); 
		} catch (MessagingException e) {
			e.printStackTrace();
		}	
	}


	public boolean getSubscriptionStatus(long userID,long reportID) throws SQLException
	{
		boolean status=false;
		try{
			statement = connect.createStatement();
			String query = "SELECT * FROM Subscription WHERE ReportID ='" + reportID +"'"+ "AND UserID = '"+userID+"'";
			resultSet = statement.executeQuery(query);
			if(resultSet.next()){
				status =  resultSet.getBoolean(5);
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return status;
	}

	public boolean subscriptionExist(long userID,long reportID) throws SQLException
	{
		boolean status=false;
		try{
			statement = connect.createStatement();
			String query = "SELECT * FROM Subscription WHERE ReportID ='" + reportID +"'"+ "AND UserID = '"+userID+"'";
			resultSet = statement.executeQuery(query);
			if(resultSet.next()){
				status =  true;
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return status;
	}

	public boolean subscriptionExistonReport(long reportID) throws SQLException
	{
		boolean status=false;
		try{
			statement = connect.createStatement();
			String query = "SELECT * FROM Subscription WHERE ReportID ='" + reportID +"'"+"AND Status = true";;
			resultSet = statement.executeQuery(query);
			if(resultSet.next()){
				status =  true;
			}
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return status;
	}



	public int getReportOwner (long reportID)
	{
		int userID=-1;

		try {
			statement=connect.createStatement();
			String query="SELECT UserID FROM Reports WHERE ReportID='" + reportID +"'";
			resultSet = statement.executeQuery(query);
			while(resultSet.next())
			{
				userID = resultSet.getInt("UserID");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return userID;

	}

	public int getUserID(String user, String pass) throws SQLException
	{
		int userID = -1;
		try{
			String query = "SELECT * FROM Users WHERE Username = ? AND password = ?";
			PreparedStatement statement = connect.prepareStatement(query);
			statement.setString(1,user);
			statement.setString(2, pass);
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				userID =  resultSet.getInt(6);
			}
			statement.close();
			resultSet.close();
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return userID;
	}

	public int getImageNum(Long reportID) throws SQLException
	{
		int imageNum = -1;
		try{
			String query = "SELECT * FROM Reports WHERE  ReportID = '"+reportID+"' ";
			PreparedStatement statement = connect.prepareStatement(query);
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				imageNum =  resultSet.getInt(12);
			}
			statement.close();
			resultSet.close();
		}
		catch(Exception e)
		{
			System.err.println ("Error message: " + e.getMessage ());
		}
		return imageNum;
	}

	public void addComment(Comment com) throws SQLException{
		statement = connect.createStatement();
		statement.executeUpdate("INSERT INTO Comments(ReportID,Text,UserID) " + "VALUES ("+com.getReportId()+",'"+ com.getText()+"',"+com.getUserId()+")");

	}

	public void addSubscription(Subscription subs) throws SQLException{
		statement = connect.createStatement();
		statement.executeUpdate("INSERT INTO Subscription(ReportID,UserID,Status) " + "VALUES ("+subs.getReportId()+",'"+ subs.getUserId()+"',"+subs.getStatus()+")");

	}

	public void changeSubscriptionStatus(boolean status, long reportID, long userID) throws SQLException{
		statement = connect.createStatement();
		String query = "UPDATE Subscription SET Status = ? WHERE ReportID = ? AND UserID = ?";
		PreparedStatement statement = connect.prepareStatement(query);
		statement.setBoolean(1, status);
		statement.setLong(2,reportID);
		statement.setLong(3,userID);
		statement.executeUpdate();
	}

	public void updateRate(int rate, long reportID) throws SQLException{
		statement = connect.createStatement();
		String query = "UPDATE Reports SET Rate = ? WHERE ReportID = ?";
		PreparedStatement statement = connect.prepareStatement(query);
		statement.setInt(1, rate);
		statement.setLong(2,reportID);
		statement.executeUpdate();
	}

	public void updateDescription(String desc, long reportID) throws SQLException{
		statement = connect.createStatement();
		String query = "UPDATE Reports SET Description = ? WHERE ReportID = ?";
		PreparedStatement statement = connect.prepareStatement(query);
		statement.setString(1, desc);
		statement.setLong(2,reportID);
		statement.executeUpdate();
	}

	public void updateImageNum(int imageNum, long reportID) throws SQLException{
		statement = connect.createStatement();
		String query = "UPDATE Reports SET ImageNum = ? WHERE ReportID = ?";
		PreparedStatement statement = connect.prepareStatement(query);
		statement.setInt(1, imageNum);
		statement.setLong(2,reportID);
		statement.executeUpdate();
	}

	public void updateStatus(int statusNo, long reportID) throws SQLException{
		statement = connect.createStatement();
		String query = "UPDATE Reports SET Status = ? WHERE ReportID = ?";
		PreparedStatement statement = connect.prepareStatement(query);
		statement.setInt(1, statusNo);
		statement.setLong(2,reportID);
		statement.executeUpdate();
	}
	public void close() throws SQLException{
		if(resultSet!=null)
		resultSet.close();
		if(statement!=null)
		statement.close();
		if(connect!=null)
		connect.close();
	}
}
